Introduction to Database Programming with Python

Course: Python Training

Topic: Introduction to Database Programming with Python

Course Link: Python Training Course at JustAcademy

Demo Link: Register for a Free Course Demo

JustAcademy Website: Visit JustAcademy

1. Introduction to Database Programming with Python

Database programming with Python is the process of connecting Python applications to databases to store, retrieve, update, and delete data. It enables developers to build applications that manage information efficiently and preserve data beyond the execution of a program.

For example, a student management application can use Python to register students, store their details in a database, retrieve marks, update contact information, and delete records when necessary.

Python supports database programming through several libraries and database drivers. Depending on the application, developers can work with SQLite, MySQL, PostgreSQL, SQL Server, and other database systems.

Why Is Database Programming Important?

• It allows applications to store data permanently.
• It makes searching and retrieving large amounts of information easier.
• It supports data modification and deletion.
• It helps multiple users and application components work with shared data.
• It supports business applications such as inventory systems, banking software, e-commerce websites, and employee management systems.
• It enables Python applications to generate reports and analyze stored information.

2. What Is a Database?

A database is an organized collection of information that can be stored, accessed, managed, and updated electronically. A database management system (DBMS) provides the software used to create and maintain databases.

Common Database Terminology

3. What Is Database Programming in Python?

Database programming in Python involves writing code that communicates with a database using a suitable library or driver. Python sends SQL statements to the database, and the database processes those statements and returns results or confirms changes.

A typical database interaction follows these steps:

1. Import the required database library.
2. Establish a database connection.
3. Create a cursor or use the library's query execution interface.
4. Execute an SQL statement.
5. Fetch results when the statement returns data.
6. Commit changes when required.
7. Close the cursor and connection, or use context managers to manage resources safely.

4. Types of Databases Used with Python

4.1 Relational Databases

Relational databases organize data into tables with rows and columns. Tables can be related using keys. Most relational database systems use SQL to create tables, retrieve records, and modify data.

Examples include:

• SQLite: A lightweight database engine that stores data in a local file and is included with Python through the sqlite3 module.
• MySQL: A widely used relational database system commonly used in web applications.
• PostgreSQL: A relational database known for its advanced SQL capabilities and extensibility.
• Microsoft SQL Server: A relational database commonly used in enterprise environments.

4.2 NoSQL Databases

NoSQL databases use data models that do not rely exclusively on traditional relational tables. Depending on the system, they may store documents, key-value pairs, graphs, or wide-column data.

Examples include MongoDB, Redis, and Apache Cassandra. Python applications typically access these databases through their corresponding client libraries.

5. Choosing a Database for a Python Application

The correct choice depends on the application's data structure, scale, deployment environment, consistency requirements, and operational needs.

6. Introduction to SQLite and the sqlite3 Module

SQLite is an excellent starting point for learning database programming because it does not require a separate database server. Python's standard library includes the sqlite3 module, which allows programs to work with SQLite databases.

Advantages of SQLite

• No separate database server is required.
• Database information can be stored in a local file.
• It is easy to set up for practice and small applications.
• It supports standard SQL features, including tables, queries, indexes, and transactions.
• It is useful for prototypes, desktop applications, testing, and local data storage.

Connecting to a Database

The connect() function creates a connection to a database file. If the file does not exist, SQLite generally creates it.

import sqlite3

connection = sqlite3.connect("college.db")
print("Database connection created successfully")

connection.close()

Explanation:

• import sqlite3 imports the database module.
• sqlite3.connect("college.db") opens or creates the database file.
• connection.close() closes the connection and releases resources.

7. Creating a Database Table

A table defines the structure in which related information will be stored. SQL's CREATE TABLE statement creates a new table.

import sqlite3

connection = sqlite3.connect("college.db")

connection.execute("""
    CREATE TABLE IF NOT EXISTS students (
        student_id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        age INTEGER,
        course TEXT
    )
""")

connection.commit()
connection.close()

print("Students table created successfully")

Understanding the Table Structure

PRIMARY KEY identifies each record uniquely. AUTOINCREMENT asks SQLite to assign increasing row IDs under its documented rules. NOT NULL prevents the name field from being left null.

8. Inserting Data into a Database

The SQL INSERT statement adds records to a table. In Python, values should be passed separately as query parameters rather than combined into SQL strings.

import sqlite3

connection = sqlite3.connect("college.db")

student = ("Aarav Sharma", 21, "Python")

connection.execute(
    "INSERT INTO students (name, age, course) VALUES (?, ?, ?)",
    student
)

connection.commit()
connection.close()

print("Student inserted successfully")

In SQLite, the question marks are parameter placeholders. The database driver safely binds the supplied values to those placeholders. The commit() method saves the transaction's changes.

Inserting Multiple Records

import sqlite3

students = [
    ("Priya Patel", 20, "Python"),
    ("Rahul Verma", 22, "Data Analytics"),
    ("Neha Singh", 19, "Web Development")
]

with sqlite3.connect("college.db") as connection:
    connection.executemany(
        "INSERT INTO students (name, age, course) VALUES (?, ?, ?)",
        students
    )

print("Multiple records inserted")

executemany() executes a parameterized statement for each item in the supplied sequence. The connection context manager commits a successful transaction or rolls it back when an exception occurs. It does not automatically close the connection, so explicitly close it or use a suitable closing pattern in longer-lived code.

9. Retrieving Data from a Database

The SQL SELECT statement retrieves information from a table. Python can fetch one record, multiple records, or all returned records.

Fetching All Records

import sqlite3

connection = sqlite3.connect("college.db")

cursor = connection.execute(
    "SELECT student_id, name, age, course FROM students"
)

rows = cursor.fetchall()

for row in rows:
    print(row)

connection.close()

fetchall() returns all remaining result rows as a list. Each row is represented as a tuple by default when using the standard SQLite cursor.

Fetching One Record

import sqlite3

with sqlite3.connect("college.db") as connection:
    cursor = connection.execute(
        "SELECT student_id, name, age, course FROM students WHERE student_id = ?",
        (1,)
    )
    student = cursor.fetchone()

    if student is not None:
        print("Student:", student)
    else:
        print("Student not found")

The trailing comma in (1,) makes the parameter a one-item tuple. A single value in parentheses without a comma is not a tuple in Python.

Filtering Results with WHERE

import sqlite3

with sqlite3.connect("college.db") as connection:
    cursor = connection.execute(
        "SELECT name, course FROM students WHERE age >= ?",
        (20,)
    )

    for row in cursor:
        print(row)

The WHERE clause limits the results to records that match the condition. Other useful SQL clauses include ORDER BY for sorting, LIMIT for limiting results, and GROUP BY for grouping data.

10. Updating Existing Records

The SQL UPDATE statement modifies data already stored in a table. A WHERE clause is usually essential to avoid accidentally updating every record.

import sqlite3

with sqlite3.connect("college.db") as connection:
    cursor = connection.execute(
        "UPDATE students SET course = ? WHERE student_id = ?",
        ("Advanced Python", 1)
    )

    print("Rows matched or changed:", cursor.rowcount)

The statement changes the course for the student whose ID is 1. The context manager commits the transaction if the block completes successfully.

11. Deleting Records

The SQL DELETE statement removes records from a table. Always verify the condition before executing a delete operation.

import sqlite3

student_id = 3

with sqlite3.connect("college.db") as connection:
    cursor = connection.execute(
        "DELETE FROM students WHERE student_id = ?",
        (student_id,)
    )

    print("Rows deleted:", cursor.rowcount)

Without a WHERE clause, a DELETE statement can remove all records from the table. The table structure itself remains in place.

12. Understanding SQL CRUD Operations

CRUD stands for Create, Read, Update, and Delete. These are the four fundamental operations used to manage records in many applications.

CRUD operations form the foundation of systems such as customer management applications, inventory software, student portals, and e-commerce platforms.

13. Using Database Cursors

A cursor is an object used by a database API to execute SQL statements and access query results. With Python's sqlite3 module, a cursor can be created explicitly using connection.cursor(), although many simple operations can be performed directly through the connection.

import sqlite3

connection = sqlite3.connect("college.db")
cursor = connection.cursor()

cursor.execute(
    "SELECT name, course FROM students"
)

for row in cursor.fetchall():
    print(row)

cursor.close()
connection.close()

Use cursors when they make the code clearer or when a database driver's API requires them. Close explicitly created cursors and database connections when they are no longer needed.

14. Parameterized Queries and SQL Injection

SQL injection occurs when untrusted input is incorporated into SQL commands in a way that changes the intended query. Attackers may use this weakness to access, alter, or delete data without authorization.

Unsafe Example

# Do not use this pattern with untrusted input.
username = input("Enter username: ")

query = "SELECT * FROM users WHERE username = '" + username + "'"

Building SQL statements through string concatenation can allow user input to change the query's meaning.

Safer Example

import sqlite3

username = input("Enter username: ")

with sqlite3.connect("college.db") as connection:
    cursor = connection.execute(
        "SELECT student_id, name FROM students WHERE name = ?",
        (username,)
    )

    for row in cursor.fetchall():
        print(row)

Parameterized queries keep SQL instructions separate from the supplied values. Use the placeholder syntax required by the specific database driver. For example, SQLite uses ?, while other drivers may use different placeholder styles.

Placeholders are intended for values, not arbitrary table names, column names, or SQL keywords. If a query must use a dynamic identifier, validate it against an explicit allowlist and construct that part carefully.

15. Transactions and Commit Operations

A transaction is a group of database operations treated as a single logical unit. Transactions help ensure that a set of changes is completed consistently or rolled back if an error occurs.

Important Transaction Concepts

• BEGIN: Starts a transaction explicitly where supported and appropriate.
• COMMIT: Makes the transaction's changes permanent.
• ROLLBACK: Reverses uncommitted changes.

Transaction Example

import sqlite3

connection = sqlite3.connect("college.db")

try:
    connection.execute(
        "INSERT INTO students (name, age, course) VALUES (?, ?, ?)",
        ("Karan Mehta", 23, "Python")
    )

    connection.execute(
        "UPDATE students SET age = ? WHERE name = ?",
        (24, "Karan Mehta")
    )

    connection.commit()
    print("Transaction completed successfully")

except sqlite3.Error as error:
    connection.rollback()
    print("Database operation failed:", error)

finally:
    connection.close()

If a database operation fails, the exception handler rolls back uncommitted changes. This example uses a single connection and demonstrates a simple transaction workflow.

16. Handling Database Errors in Python

Database programs may encounter errors such as invalid SQL syntax, constraint violations, unavailable files, locked databases, or failed connections. Python provides exception handling to manage these situations without allowing every database error to terminate the application unexpectedly.

import sqlite3

try:
    with sqlite3.connect("college.db") as connection:
        connection.execute(
            "INSERT INTO students (name, age, course) VALUES (?, ?, ?)",
            ("Anaya Rao", 20, "Python")
        )

    print("Record saved successfully")

except sqlite3.IntegrityError as error:
    print("A database constraint was violated:", error)

except sqlite3.OperationalError as error:
    print("A database operation failed:", error)

except sqlite3.Error as error:
    print("Database error:", error)

Catch specific exceptions when you can respond meaningfully to them. Avoid silently ignoring database errors, and avoid exposing sensitive internal details to end users in production applications.

17. Using MySQL with Python

SQLite is convenient for learning, but many web and business applications use a database server such as MySQL. A MySQL connector allows Python to establish a connection and execute SQL statements on a MySQL server.

Installing MySQL Connector/Python

python -m pip install mysql-connector-python

Run this command in your terminal or virtual environment. The database server must also be installed or otherwise available, and the application needs valid connection details.

Connecting to MySQL

import mysql.connector

connection = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="college"
)

try:
    if connection.is_connected():
        print("Connected to MySQL successfully")
finally:
    connection.close()

Replace the example connection details with your actual configuration. Do not put real passwords into publicly shared source code. Use environment variables or a secure secrets-management solution for credentials.

Executing a MySQL Query

import mysql.connector

connection = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="college"
)

cursor = None

try:
    cursor = connection.cursor()

    cursor.execute(
        "SELECT student_id, name, course FROM students WHERE age >= %s",
        (20,)
    )

    for row in cursor.fetchall():
        print(row)

finally:
    if cursor is not None:
        cursor.close()
    connection.close()

In this example, %s is the parameter placeholder used by MySQL Connector/Python. Placeholder syntax differs by driver, so consult the relevant driver's documentation rather than copying SQLite syntax unchanged.

18. Python Database APIs and Database Drivers

Python database drivers provide the connection and query interfaces needed to work with specific database systems. Many relational database drivers follow Python's DB-API 2.0 conventions, which standardize common operations such as connecting, executing statements, and fetching results.

The best library depends on the database system and the requirements of the application. Consult each library's official documentation for installation, connection options, parameter styles, and supported features.

19. Using Database Programming in Real-World Applications

Python database programming is used in many practical projects.

• Student Management System: Stores student profiles, course registrations, and marks.
• Inventory Management: Tracks products, stock quantities, suppliers, and sales.
• Employee Management: Maintains employee information, departments, and attendance records.
• E-Commerce Website: Stores products, customers, orders, and order details.
• Expense Tracker: Records transactions, categories, dates, and spending summaries.
• Reporting Application: Queries stored information and produces summaries for business users.

20. Mini Project: Student Management System

This beginner project demonstrates how to create a database table and implement basic CRUD operations with Python and SQLite.

Project Features

• Create a student record.
• Display all student records.
• Search for a student by ID.
• Update a student's course.
• Delete a student record.

Complete Example

import sqlite3

DATABASE = "student_management.db"


def initialize_database():
    with sqlite3.connect(DATABASE) as connection:
        connection.execute("""
            CREATE TABLE IF NOT EXISTS students (
                student_id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL,
                age INTEGER NOT NULL,
                course TEXT NOT NULL
            )
        """)


def add_student(name, age, course):
    with sqlite3.connect(DATABASE) as connection:
        connection.execute(
            "INSERT INTO students (name, age, course) VALUES (?, ?, ?)",
            (name, age, course)
        )
    print("Student added successfully.")


def view_students():
    with sqlite3.connect(DATABASE) as connection:
        rows = connection.execute(
            "SELECT student_id, name, age, course FROM students ORDER BY student_id"
        ).fetchall()

    if not rows:
        print("No students found.")
        return

    for student in rows:
        print(student)


def search_student(student_id):
    with sqlite3.connect(DATABASE) as connection:
        student = connection.execute(
            "SELECT student_id, name, age, course FROM students WHERE student_id = ?",
            (student_id,)
        ).fetchone()

    if student:
        print("Student:", student)
    else:
        print("Student not found.")


def update_course(student_id, new_course):
    with sqlite3.connect(DATABASE) as connection:
        cursor = connection.execute(
            "UPDATE students SET course = ? WHERE student_id = ?",
            (new_course, student_id)
        )

    if cursor.rowcount:
        print("Course updated successfully.")
    else:
        print("Student not found.")


def delete_student(student_id):
    with sqlite3.connect(DATABASE) as connection:
        cursor = connection.execute(
            "DELETE FROM students WHERE student_id = ?",
            (student_id,)
        )

    if cursor.rowcount:
        print("Student deleted successfully.")
    else:
        print("Student not found.")


def main():
    initialize_database()

    while True:
        print("\\n--- Student Management System ---")
        print("1. Add Student")
        print("2. View Students")
        print("3. Search Student")
        print("4. Update Course")
        print("5. Delete Student")
        print("6. Exit")

        choice = input("Enter your choice: ").strip()

        if choice == "1":
            name = input("Enter student name: ").strip()
            try:
                age = int(input("Enter student age: "))
            except ValueError:
                print("Age must be a valid integer.")
                continue

            course = input("Enter course: ").strip()

            if not name or not course or age <= 0:
                print("Enter a valid name, positive age, and course.")
                continue

            add_student(name, age, course)

        elif choice == "2":
            view_students()

        elif choice == "3":
            try:
                student_id = int(input("Enter student ID: "))
                search_student(student_id)
            except ValueError:
                print("Student ID must be an integer.")

        elif choice == "4":
            try:
                student_id = int(input("Enter student ID: "))
            except ValueError:
                print("Student ID must be an integer.")
                continue

            new_course = input("Enter new course: ").strip()

            if new_course:
                update_course(student_id, new_course)
            else:
                print("Course cannot be empty.")

        elif choice == "5":
            try:
                student_id = int(input("Enter student ID: "))
                delete_student(student_id)
            except ValueError:
                print("Student ID must be an integer.")

        elif choice == "6":
            print("Exiting application.")
            break

        else:
            print("Invalid choice. Please try again.")


if __name__ == "__main__":
    main()

How the Project Works

1. The initialize_database() function creates the database table if it does not already exist.
2. The add_student() function inserts a new student using a parameterized query.
3. The view_students() function retrieves and displays all student records.
4. The search_student() function retrieves one student by their ID.
5. The update_course() function modifies the course for a selected student.
6. The delete_student() function removes a selected student record.
7. The main() function displays the menu and handles user input.

Practice task: Extend this project by adding email addresses, checking for duplicate student records, validating input more thoroughly, and adding a search feature by student name.

21. Best Practices for Python Database Programming

• Use parameterized queries: Never build SQL statements by concatenating untrusted input.
• Manage connections carefully: Close connections and cursors when they are no longer needed.
• Use transactions: Group related changes together when consistency matters.
• Handle exceptions: Catch appropriate database exceptions and provide useful error handling.
• Protect credentials: Store secrets outside source code and restrict access to them.
• Apply database constraints: Use primary keys, foreign keys, unique constraints, and appropriate nullability rules.
• Validate user input: Check data types, required fields, and acceptable values.
• Use indexes thoughtfully: Index frequently searched or joined columns when query performance benefits, while considering the cost of additional indexes.
• Back up important data: Follow a backup and recovery plan appropriate for the database.
• Limit database permissions: Give application accounts only the privileges they need.
• Avoid unnecessary queries: Retrieve only the columns and records required by the application.
• Test database code: Use a separate test database and verify successful operations as well as failure cases.

22. Common Errors and Troubleshooting

23. Interview Questions and Answers

Q1. What is database programming in Python?

Answer: It is the process of using Python libraries or database drivers to connect to a database and perform operations such as inserting, retrieving, updating, and deleting data.

Q2. What is a DBMS?

Answer: A Database Management System is software used to create, organize, query, secure, and maintain databases.

Q3. What is SQLite?

Answer: SQLite is a lightweight relational database engine that stores a database in a file and can be accessed in Python through the built-in sqlite3 module.

Q4. How do you connect Python to SQLite?

Answer: Import sqlite3 and call sqlite3.connect("database.db") to obtain a connection.

Q5. What is a cursor?

Answer: A cursor is a database API object used to execute SQL statements and access query results.

Q6. What is the difference between execute() and executemany()?

Answer: execute() runs a statement with one set of parameters, while executemany() applies a statement to multiple parameter sets.

Q7. What does commit() do?

Answer: It commits the current transaction, making its changes permanent according to the database's transaction rules.

Q8. What does rollback() do?

Answer: It reverses changes made in the current uncommitted transaction.

Q9. What is SQL injection?

Answer: SQL injection is a security vulnerability in which untrusted input alters the intended meaning of an SQL statement. Parameterized queries are a primary defense.

Q10. Why should parameterized queries be used?

Answer: They separate SQL instructions from data values, reducing SQL injection risk and handling values correctly through the database driver.

Q11. What is the difference between SQLite and MySQL?

Answer: SQLite is an embedded database commonly stored in a local file, whereas MySQL is a database server designed to support client connections and shared database workloads.

Q12. What are CRUD operations?

Answer: CRUD means Create, Read, Update, and Delete. These operations cover the basic lifecycle of stored records.

Q13. Why is a primary key important?

Answer: A primary key uniquely identifies each row and helps maintain record integrity and relationships.

Q14. What is a transaction?

Answer: A transaction groups operations into one logical unit so that related changes can be committed together or rolled back when necessary.

Q15. How can database credentials be protected?

Answer: Keep credentials out of source control, use environment variables or a secrets manager, apply least-privilege permissions, and protect configuration files.

24. Practical Exercises

1. Create a database named library.db and a table named books.
2. Add columns for book ID, title, author, and publication year.
3. Insert at least five book records using parameterized queries.
4. Display all books in alphabetical order by title.
5. Retrieve books published after a specified year.
6. Update the author of a selected book.
7. Delete a book using its ID.
8. Handle attempts to retrieve IDs that do not exist.
9. Write a transaction that performs two related database changes and rolls back if an operation fails.
10. Build a menu-driven library management application with add, view, search, update, and delete options.

25. Quick Revision Checklist

• Understand databases, DBMSs, tables, rows, columns, and keys.
• Know the differences between relational and NoSQL databases.
• Connect Python to SQLite using sqlite3.connect().
• Create tables using CREATE TABLE.
• Insert records with INSERT and retrieve data with SELECT.
• Modify records using UPDATE and remove records using DELETE.
• Use parameterized queries to safely supply values.
• Understand cursors, result fetching, commits, rollbacks, and transactions.
• Handle database exceptions and manage connections correctly.
• Understand how a Python driver connects to MySQL or PostgreSQL.
• Apply validation, access control, backups, and testing to practical applications.

26. Additional Learning Resources

• Explore the Python Training Course at JustAcademy
• Register for a Course Demo at JustAcademy
• Visit the JustAcademy Homepage
• Official Python sqlite3 Documentation
• Python Database API Specification (DB-API 2.0)
• Official MySQL Connector/Python Documentation
• Official SQLite Documentation

Conclusion

Database programming is an essential Python skill for developing applications that store and manage persistent information. By learning how to connect to databases, create tables, execute SQL queries, implement CRUD operations, handle transactions, and protect against SQL injection, developers can build reliable data-driven applications.

Start with SQLite and the built-in sqlite3 module to understand the fundamentals. Then practice with a database server such as MySQL or PostgreSQL, and build projects that demonstrate database design, validation, error handling, and secure query execution.

For more structured learning, visit JustAcademy's Python Training Course or register for a course demo.